package com.bw.mapper;

import com.bw.bean.Vo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;


@Mapper
public interface VoMapper {

    @Select("call p1;")
    Double getList1();


    @Select("select  bp.name,sum(final_amount)value from  order_info oi\n" +
            "left join week3.base_province bp on oi.province_id = bp.id\n" +
            "group by bp.name;")
    List<Vo> getList2();

    @Select("select tp.paper_title name,avg(te.score)value from  test_exam te\n" +
            "left join test_paper tp on paper_id=tp.id\n" +
            "group by tp.paper_title having value>60;")
    List<Vo> getList3();

    @Select("select max(score)-min(score) from  test_exam;")
    List<Vo> getList4();

    @Select("select * from  test_exam where score =(select max(score) from  test_exam);")
    List<Vo> getList5();

    @Select("select order_status,count(id) from  order_info group by order_status;")
    List<Vo> getList6();

    @Select("select b.name,COUNT(DISTINCT user_id) 'value' from order_info o\n" +
            "LEFT JOIN base_province b on o.province_id=b.id\n" +
            "GROUP BY b.name HAVING value>10000;")
    List<Vo> getList7();
}
